ALTER TABLE HYDRA_MESSAGE ADD COLUMN COD_HYDRA_REQUEST NUMERIC(20,0);
ALTER TABLE HYDRA_MESSAGE ADD COLUMN MESSAGE_DIRECTION NUMERIC(1,0);
COMMENT ON COLUMN HYDRA_MESSAGE.COD_HYDRA_REQUEST IS 'CODIGO DA REQUISICAO QUE TRATOU ESTA MENSAGEM.';
COMMENT ON COLUMN HYDRA_MESSAGE.MESSAGE_DIRECTION IS 'DIRECAO DA MENSAGEM. 0 - RECEBIDA; 1 - ENVIADA';
ALTER TABLE HYDRA_MESSAGE ADD CONSTRAINT CK_HYDMSG_0 CHECK (MESSAGE_DIRECTION IN (0, 1));
ALTER TABLE HYDRA_MESSAGE ADD CONSTRAINT FK_HYDREQ_HYDMSG FOREIGN KEY (COD_HYDRA_REQUEST) REFERENCES HYDRA_REQUEST (COD_HYDRA_REQUEST);

CREATE OR REPLACE FUNCTION MIGRA_DADOS() RETURNS INTEGER AS
$BODY$
DECLARE
  V_COD_HYDRA_MESSAGE NUMERIC(20,0);
  V_COD_HYDRA_ACTION  NUMERIC(20,0);
  V_COD_HYDRA_REQUEST NUMERIC(20,0);
  V_ACTION RECORD;
  V_MESSAGE RECORD;
BEGIN
  FOR V_MESSAGE IN SELECT * FROM HYDRA_MESSAGE LOOP
    RAISE NOTICE 'MESSAGE %s', V_MESSAGE.COD_HYDRA_MESSAGE;
    SELECT INTO V_ACTION * FROM HYDRA_ACTION WHERE COD_HYDRA_MESSAGE = V_MESSAGE.COD_HYDRA_MESSAGE AND TP_HYDRA_ACTION IN (0, 5);
    IF V_ACTION.TP_HYDRA_ACTION = 0 THEN
      UPDATE HYDRA_MESSAGE SET MESSAGE_DIRECTION = 0 WHERE COD_HYDRA_MESSAGE = V_MESSAGE.COD_HYDRA_MESSAGE;
    ELSE
      UPDATE HYDRA_MESSAGE SET MESSAGE_DIRECTION = 1 WHERE COD_HYDRA_MESSAGE = V_MESSAGE.COD_HYDRA_MESSAGE;
    END IF;
    WHILE V_ACTION.COD_HYDRA_ACTION_PAI IS NOT NULL LOOP
      SELECT INTO V_ACTION * FROM HYDRA_ACTION WHERE COD_HYDRA_ACTION = V_ACTION.COD_HYDRA_ACTION_PAI;
    END LOOP;
    SELECT COD_HYDRA_REQUEST INTO V_COD_HYDRA_REQUEST FROM HYDRA_REQUEST WHERE COD_HYDRA_ACTION = V_ACTION.COD_HYDRA_ACTION;
    UPDATE HYDRA_MESSAGE SET COD_HYDRA_REQUEST = V_COD_HYDRA_REQUEST WHERE COD_HYDRA_MESSAGE = V_MESSAGE.COD_HYDRA_MESSAGE;
  END LOOP;
  RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

SELECT MIGRA_DADOS();

DROP FUNCTION MIGRA_DADOS();

ALTER TABLE HYDRA_ACTION DROP COLUMN COD_HYDRA_MESSAGE;
ALTER TABLE HYDRA_MESSAGE ALTER COLUMN MESSAGE_DIRECTION SET NOT NULL;
